Imports System.Data.OracleClient

Public Class clsAddNewTable

#Region " CLASS ENUMS "

  Private Enum EnmTableSpaceTypes
    USERS
    INDX
    TEMP
  End Enum

#End Region

#Region " CLASS MEMBER VARIABLES "

  Private mCmdData As OracleCommand
  Private mDrData As OracleDataReader
  Private mIntMaxTableId As Integer
  Private mIntTableId As Integer
  Private mStrTableName As String
  Private mArrColumnName(0) As String
  Private mArrColumnDataType(0) As String
  Private mArrColumnNullIndicator(0) As String
  Private mArrDataLength(0) As Integer
  Private mArrDataPrecision(0) As Integer
  Private mArrBasicDataType(0) As String
  Private mArrFrontEndDataType(0) As String
  Private mArrBackEndDataType(0) As String
  Private mArrFrontEndDataTypeClass(0) As String
  Private mArrClntSrvrIndc(0) As String
  Private mArrVrblRef(0) As Integer
  Private mBlnRgnrt As Boolean
#End Region

#Region " CLASS MEMBER FUNCTIONS "

  Public Function mPrAddNewTable _
                     (ByVal aStrNewTableName As String _
                    , ByVal aStrTableSpaceType As String _
                    , ByVal aStrClntSrvrIndc As String _
                    , ByVal aStrTableShortName As String _
                    , ByVal aStrTableDesc As String _
                    , ByVal aBlnModifyTable As Boolean _
                    , ByVal aBlnRgnrtTable As Boolean) As Integer

    Dim lStrQuery As String
    Dim lIntExistingTableId As Integer
    Dim lIntTableSpaceId As Integer


    If aStrNewTableName <> "" And aStrTableSpaceType <> "" Then

      mPrRefreshConn()
      lStrQuery = "Select max(TABM_TABLE_ID) from TRDX_TABLES_MSTR"
      mCmdData = New OracleCommand(lStrQuery, con)
      mIntMaxTableId = mCmdData.ExecuteScalar

      If mFnReturnEnumTableSpaces(aStrTableSpaceType) = EnmTableSpaceTypes.USERS Then
        lIntTableSpaceId = 1
      ElseIf mFnReturnEnumTableSpaces(aStrTableSpaceType) = EnmTableSpaceTypes.INDX Then
        lIntTableSpaceId = 2
      ElseIf mFnReturnEnumTableSpaces(aStrTableSpaceType) = EnmTableSpaceTypes.TEMP Then
        lIntTableSpaceId = 3
      End If

      If mIntMaxTableId <> 0 Then

        If aBlnRgnrtTable = False Then
          lStrQuery = ""
          lStrQuery = "Select TABM_TABLE_ID from TRDX_TABLES_MSTR "
          lStrQuery = lStrQuery & " Where TABM_TABLE_NAME = "
          lStrQuery = lStrQuery & "'" & aStrNewTableName & "'"

          mCmdData = New OracleCommand(lStrQuery, con)
          lIntExistingTableId = mCmdData.ExecuteScalar
        Else
          lIntExistingTableId = 0
        End If

        If lIntExistingTableId <> 0 And aBlnModifyTable = False Then
          ' MessageBox.Show("Table Already Exists")
          lStrQuery = ""
          lStrQuery = "Update TRDX_TABLES_MSTR Set"
          lStrQuery = lStrQuery & " TABM_TABLE_ID = " & lIntExistingTableId
          lStrQuery = lStrQuery & " , TABM_TABLESPACE_ID = " & lIntTableSpaceId
          lStrQuery = lStrQuery & " , TABM_SYSTEM_INDC = '" & SYSTEM_INDC & "'"
          lStrQuery = lStrQuery & " , TABM_CLNT_SRVR_INDC = '" & aStrClntSrvrIndc & "'"
          lStrQuery = lStrQuery & " , TABM_TABLE_SHORT_NAME = '" & aStrTableShortName & "'"
          lStrQuery = lStrQuery & " Where TABM_TABLE_ID = " & lIntExistingTableId

          mCmdData = New OracleCommand(lStrQuery, con)
          mCmdData.ExecuteNonQuery()

          lStrQuery = ""
          lStrQuery = "UPDATE TRDX_TABLES_DTLS SET "
          lStrQuery = lStrQuery & "TTD_TABLE_ID = " & lIntExistingTableId & _
                                  ", TTD_TABLE_DESC = '" & aStrTableDesc & _
                                  " ', TTD_CNFG_INDC = 'N', TTD_HSTR_INDC = 'N'" & _
                                  " WHERE TTD_TABLE_ID = " & lIntExistingTableId

          mCmdData = New OracleCommand(lStrQuery, con)
          mCmdData.ExecuteNonQuery()

          Return lIntExistingTableId
        ElseIf aBlnModifyTable = False Then
          lStrQuery = ""
          lStrQuery = "Insert into TRDX_TABLES_MSTR "
          lStrQuery = lStrQuery & "( TABM_TABLE_ID "
          lStrQuery = lStrQuery & " , TABM_TABLE_NAME "
          lStrQuery = lStrQuery & " , TABM_TABLESPACE_ID "
          lStrQuery = lStrQuery & " , TABM_SYSTEM_INDC "
          lStrQuery = lStrQuery & " , TABM_CLNT_SRVR_INDC, TABM_TABLE_SHORT_NAME )"

          lStrQuery = lStrQuery & " Values "
          lStrQuery = lStrQuery & " ( "
          lStrQuery = lStrQuery & mIntMaxTableId + 1
          lStrQuery = lStrQuery & ", '"
          lStrQuery = lStrQuery & aStrNewTableName & "'"
          lStrQuery = lStrQuery & "," & lIntTableSpaceId
          lStrQuery = lStrQuery & ",'" & SYSTEM_INDC & "'"
          lStrQuery = lStrQuery & ",'" & aStrClntSrvrIndc & "'"
          lStrQuery = lStrQuery & ",'" & aStrTableShortName & "')"

          mCmdData = New OracleCommand(lStrQuery, con)
          mCmdData.ExecuteNonQuery()

          lStrQuery = ""
          lStrQuery = "INSERT INTO TRDX_TABLES_DTLS "
          lStrQuery = lStrQuery & "( TTD_TABLE_ID , TTD_TABLE_DESC , TTD_CNFG_INDC , TTD_HSTR_INDC) VALUES "
          lStrQuery = lStrQuery & " ( " & mIntMaxTableId + 1 & ",'" & aStrTableDesc & " ','N','N')"

          mCmdData = New OracleCommand(lStrQuery, con)
          mCmdData.ExecuteNonQuery()

          mIntTableId = mIntMaxTableId + 1

        ElseIf lIntExistingTableId <> 0 And aBlnModifyTable = True Then

          ' Do nothing, the table being modified already exists. 
          'We will re-enter the column names for the same table id

          mIntTableId = lIntExistingTableId
          lStrQuery = ""
          lStrQuery = "Update TRDX_TABLES_MSTR Set"
          lStrQuery = lStrQuery & " TABM_TABLE_ID = " & lIntExistingTableId
          lStrQuery = lStrQuery & " , TABM_TABLESPACE_ID = " & lIntTableSpaceId
          lStrQuery = lStrQuery & " , TABM_SYSTEM_INDC = '" & SYSTEM_INDC & "'"
          lStrQuery = lStrQuery & " , TABM_CLNT_SRVR_INDC = '" & aStrClntSrvrIndc & "'"
          lStrQuery = lStrQuery & " , TABM_TABLE_SHORT_NAME = '" & aStrTableShortName & "'"
          lStrQuery = lStrQuery & " Where TABM_TABLE_ID = " & lIntExistingTableId

          mCmdData = New OracleCommand(lStrQuery, con)
          mCmdData.ExecuteNonQuery()

          lStrQuery = ""
          lStrQuery = "UPDATE TRDX_TABLES_DTLS SET "
          lStrQuery = lStrQuery & "TTD_TABLE_ID = " & lIntExistingTableId & _
                                  ", TTD_TABLE_DESC = '" & aStrTableDesc & _
                                  " ', TTD_CNFG_INDC = 'N', TTD_HSTR_INDC = 'N'" & _
                                  " WHERE TTD_TABLE_ID = " & lIntExistingTableId

          mCmdData = New OracleCommand(lStrQuery, con)
          mCmdData.ExecuteNonQuery()

          Return mIntTableId
        End If
      End If
    Else
      MessageBox.Show("Please Enter Table Name to Add")
      Exit Function
    End If
    Return mIntTableId
  End Function

  Public Function mPrAddTableDetails(ByVal aStrTableName As String _
                              , ByVal aStrTableShortName As String _
                              , ByVal aCtlDataGridView As DataGridView) As Boolean

    Dim lBlnRtn As Boolean
    Dim lStrQuery As String = ""
    ' Dim lArrStrDataType(0) As String
    Dim lStrDataType As String = ""
    Dim lStrColumnName As String = ""
    Dim lStrTableShortName As String = ""
    Dim lIntDataTypeArrayLength As Integer = 0
    Dim lIntColumnId As Integer = 0
    Dim lIntDataGridViewRowCount As Integer
    Dim lIntCounter As Integer
    Dim lIntDataLength As Integer = 0
    Dim lIntDataPrecision As Integer = 0
    Dim lLstPKColms As New ListBox

    If mIntTableId <> 0 Then

      lStrTableShortName = aStrTableShortName

      lIntDataGridViewRowCount = aCtlDataGridView.RowCount - 1

      ReDim Preserve mArrColumnDataType(lIntDataGridViewRowCount)
      ReDim Preserve mArrColumnName(lIntDataGridViewRowCount)
      ReDim Preserve mArrColumnNullIndicator(lIntDataGridViewRowCount)
      ReDim Preserve mArrDataLength(lIntDataGridViewRowCount)
      ReDim Preserve mArrDataPrecision(lIntDataGridViewRowCount)
      ReDim Preserve mArrBasicDataType(lIntDataGridViewRowCount)
      ReDim Preserve mArrFrontEndDataType(lIntDataGridViewRowCount)
      ReDim Preserve mArrBackEndDataType(lIntDataGridViewRowCount)
      ReDim Preserve mArrFrontEndDataTypeClass(lIntDataGridViewRowCount)
      ReDim Preserve mArrClntSrvrIndc(lIntDataGridViewRowCount)
      ReDim Preserve mArrVrblRef(lIntDataGridViewRowCount)

      For lIntCounter = 0 To lIntDataGridViewRowCount

        With aCtlDataGridView

          mArrColumnName(lIntCounter) = .Rows(lIntCounter).Cells("clmClmName").Value
          mArrColumnDataType(lIntCounter) = .Rows(lIntCounter).Cells("clmClmTypeValueMember").Value
          mArrColumnNullIndicator(lIntCounter) = .Rows(lIntCounter).Cells("clmNull").Value
          mArrDataLength(lIntCounter) = .Rows(lIntCounter).Cells("clmLength").Value
          If IsDBNull(.Rows(lIntCounter).Cells("clmPrecision").Value) Then
            mArrDataPrecision(lIntCounter) = 0
          Else
            mArrDataPrecision(lIntCounter) = .Rows(lIntCounter).Cells("clmPrecision").Value
          End If
          mArrBasicDataType(lIntCounter) = .Rows(lIntCounter).Cells("clmBasicDT").Value
          mArrFrontEndDataType(lIntCounter) = .Rows(lIntCounter).Cells("clmFEDataType").Value
          mArrBackEndDataType(lIntCounter) = .Rows(lIntCounter).Cells("clmBEDataType").Value
          mArrFrontEndDataTypeClass(lIntCounter) = .Rows(lIntCounter).Cells("clmFECategory").Value
          mArrClntSrvrIndc(lIntCounter) = .Rows(lIntCounter).Cells("clmClientSrvIndc").Value
          mArrVrblRef(lIntCounter) = .Rows(lIntCounter).Cells("clmVariableReference").Value
          If .Rows(lIntCounter).Cells("clmPK").Value = "Y" And .Rows(lIntCounter).Cells("clmNull").Value = "Y" Then
            lLstPKColms.Items.Add(.Rows(lIntCounter).Cells("clmClmName").Value)
          ElseIf .Rows(lIntCounter).Cells("clmPK").Value = "Y" And .Rows(lIntCounter).Cells("clmNull").Value = "N" Then
            MsgBox("Primary Key column must be Not Null", MsgBoxStyle.Information, gCONST_MSG_HEADER)
            Return False
          End If
        End With

      Next

      For lIntCounter = 0 To lIntDataGridViewRowCount

        lIntColumnId = lIntColumnId + 1

        lStrColumnName = mArrColumnName(lIntCounter)

        lStrDataType = mArrFrontEndDataTypeClass(lIntCounter)
        'lStrDataType = "cls" & mFnGetColDataType(mArrBasicDataType(lIntCounter))

        If UCase(mArrBasicDataType(lIntCounter).ToString) = "VARCHAR2" Or _
            UCase(mArrBasicDataType(lIntCounter).ToString) = "DATE" Then
          mArrBasicDataType(lIntCounter) = "Y"
        Else
          mArrBasicDataType(lIntCounter) = "N"
        End If

        lStrQuery = "Insert Into TRDX_TABLE_COLUMNS ("
        lStrQuery = lStrQuery & "  TAB_TABLE_ID "
        lStrQuery = lStrQuery & ", TAB_COL_ID"
        lStrQuery = lStrQuery & ", TAB_COL_NAME"
        lStrQuery = lStrQuery & ", TAB_COL_SEQ_NO"
        lStrQuery = lStrQuery & ", TAB_COL_DATA_TYPE"
        lStrQuery = lStrQuery & ", TAB_COL_NULL_INDC"
        lStrQuery = lStrQuery & ", TAB_COL_DATA_LENGTH"
        lStrQuery = lStrQuery & ", TAB_COL_DATA_PRECISION"
        lStrQuery = lStrQuery & ", TAB_COL_BASIC_DATA_TYPE "
        lStrQuery = lStrQuery & ",TAB_VRBL_SERIAL_NMBR)"
        lStrQuery = lStrQuery & " Values ( "
        lStrQuery = lStrQuery & mIntTableId
        lStrQuery = lStrQuery & ", " & lIntColumnId
        lStrQuery = lStrQuery & ",'" & lStrColumnName & "'"
        lStrQuery = lStrQuery & ", " & lIntColumnId
        lStrQuery = lStrQuery & ",'" & lStrDataType & "'"
        lStrQuery = lStrQuery & ",'" & UCase(mArrColumnNullIndicator(lIntCounter)) & "'"
        lStrQuery = lStrQuery & ", " & mArrDataLength(lIntCounter)
        lStrQuery = lStrQuery & ", " & mArrDataPrecision(lIntCounter)
        lStrQuery = lStrQuery & ",'" & mArrBasicDataType(lIntCounter) & "'"
        lStrQuery = lStrQuery & ",'" & mArrVrblRef(lIntCounter) & "' )"

        Try
          mCmdData = New OracleCommand(lStrQuery, con)
          mCmdData.ExecuteNonQuery()
          lBlnRtn = True
        Catch ex As Exception
          lBlnRtn = False
        End Try

        ' ReDim lArrStrDataType(0)
        lStrColumnName = ""
        lStrDataType = ""

      Next

      lBlnRtn = lBlnRtn And mPrAddPrimaryKeyDetails(lLstPKColms, mIntTableId, PRIMARY_KEY_INDC)

      'MessageBox.Show("Table Inserted Successfully !! Please Enter Primary Key ")
      lStrTableShortName = ""
    Else
      'MessageBox.Show("Table Does Not Exists")
    End If
    Return lBlnRtn

  End Function

  Public Function mFnPopulatePrimaryKeySelectionColumn(ByVal aStrTableName As String _
                                                     , ByRef aIntTableId As Integer) As ArrayList

    Dim lStrQuery As String = ""
    Dim lIntTableId As Integer
    Dim lArrayList As New ArrayList


    If aStrTableName <> "" Then

      lStrQuery = "Select TABM_TABLE_ID From TRDX_TABLES_MSTR "
      lStrQuery = lStrQuery & "Where TABM_TABLE_NAME = "
      lStrQuery = lStrQuery & "'" & aStrTableName & "'"

      mCmdData = New OracleCommand(lStrQuery, con)
      lIntTableId = mCmdData.ExecuteScalar

      If lIntTableId <> 0 Then

        lStrQuery = ""
        lStrQuery = "Select TAB_COL_NAME From TRDX_TABLE_COLUMNS "
        lStrQuery = lStrQuery & "Where TAB_TABLE_ID = "
        lStrQuery = lStrQuery & lIntTableId

        mCmdData = New OracleCommand(lStrQuery, con)
        mDrData = mCmdData.ExecuteReader

        If IsDBNull(mDrData) = False Then
          While mDrData.Read
            lArrayList.Add(mDrData("TAB_COL_NAME"))
          End While
        Else
          MessageBox.Show("No Column Record Exist for : " & aStrTableName)
        End If
      End If
    Else
      MessageBox.Show("Please Select Table Name ")
    End If
    aIntTableId = lIntTableId
    Return lArrayList

  End Function

  Public Function mPrAddPrimaryKeyDetails(ByVal aLstSelectedColumns As ListBox _
                                                                        , ByVal aIntTableId As Integer _
                                                                        , ByVal aStrKeyType As String) As Boolean

    Dim lBlnRtn As Boolean
    Dim lStrQuery As String = ""
    Dim lIntColumnId As Integer
    Dim lIntIndexId As Integer
    Dim lStrKeyType As String = ""
    Dim lIntColumnIndex As Integer = 0
    Dim lIntCount As Integer = 0
    Dim lIntEffectedRowCount As Integer = 0
    Dim lStrTableShrtName As String = String.Empty

    If aStrKeyType = "" Then
      MessageBox.Show("Please Select Key Type")
      Return False
    End If

    Select Case aStrKeyType

      Case PRIMARY_KEY_INDC
        lStrKeyType = PRIMARY_KEY_INDICATOR

      Case FOREIGN_KEY_INDC
        lStrKeyType = FOREIGN_KEY_INDICATOR

      Case UNIQUE_KEY_INDC
        lStrKeyType = UNIQUE_KEY_INDICATOR
    End Select


    If aIntTableId <> 0 Then

      lStrQuery = "Select Count(*) From TRDX_TABL_INDX "
      lStrQuery = lStrQuery & " Where TTL_TABL_IDXX = " & aIntTableId.ToString
      lStrQuery = lStrQuery & " and  TTL_INDX_TYPE = '" & lStrKeyType & "'"

      Try
        mCmdData = New OracleCommand(lStrQuery, con)
        lIntCount = mCmdData.ExecuteScalar
        lBlnRtn = True
      Catch ex As Exception
        lBlnRtn = False
      End Try

      If lIntCount <> 0 Then

        'If MsgBox("This Table Already has column constraints assigned ... " & _
        '                    "Do you want to modify the entries? " & _
        '                    "You will need to recreate all the " & aStrKeyType & " constraints ", _
        '                    MsgBoxStyle.YesNo Or MsgBoxStyle.Question, "Infomation") = MsgBoxResult.No Then
        '  Return False
        'Else

        lBlnRtn = lBlnRtn Or mPrRemoveTableIndexDetails(aIntTableId, lStrKeyType)

        'End If

      End If

      For lIntColumnIndex = 0 To aLstSelectedColumns.Items.Count - 1

        lStrQuery = "SELECT TAB_COL_ID "
        lStrQuery = lStrQuery & "FROM TRDX_TABLE_COLUMNS "
        lStrQuery = lStrQuery & "WHERE TAB_TABLE_ID = "
        lStrQuery = lStrQuery & aIntTableId
        lStrQuery = lStrQuery & " AND TAB_COL_NAME = "
        lStrQuery = lStrQuery & "'" & aLstSelectedColumns.Items.Item(lIntColumnIndex) & "'"

        Try
          mCmdData = New OracleCommand(lStrQuery, con)
          mDrData = mCmdData.ExecuteReader
          lBlnRtn = lBlnRtn And True
        Catch ex As Exception
          lBlnRtn = lBlnRtn And False
        End Try

        If IsDBNull(mDrData) = False Then

          While mDrData.Read
            lIntColumnId = mDrData("TAB_COL_ID")
          End While

          lStrQuery = ""
          lStrQuery = "SELECT MAX(TTL_INDX_IDXX) FROM TRDX_TABL_INDX"
          mCmdData = New OracleCommand(lStrQuery, con)
          lIntIndexId = mCmdData.ExecuteScalar

          lStrQuery = "SELECT TABM_TABLE_SHORT_NAME FROM TRDX_TABLES_MSTR WHERE TABM_TABLE_ID=" & aIntTableId
          mCmdData = New OracleCommand(lStrQuery, con)
          lStrTableShrtName = mCmdData.ExecuteScalar


          If IsDBNull(mCmdData.ExecuteScalar) = True Then
            lIntIndexId = 1
          Else
            lIntIndexId = lIntIndexId + 1
          End If

          lStrQuery = "Insert Into TRDX_TABL_INDX ("
          lStrQuery = lStrQuery & "  TTL_TABL_IDXX "
          lStrQuery = lStrQuery & ", TTL_INDX_IDXX "
          lStrQuery = lStrQuery & ", TTL_INDX_TYPE "
          lStrQuery = lStrQuery & ", TTL_COLM_IDXX "
          lStrQuery = lStrQuery & ", TTL_COLM_SEQC_NMBR"
          lStrQuery = lStrQuery & ", ttl_indx_name)"
          lStrQuery = lStrQuery & " Values ( "
          lStrQuery = lStrQuery & aIntTableId
          lStrQuery = lStrQuery & ", " & lIntIndexId
          lStrQuery = lStrQuery & ",'" & lStrKeyType & "'"
          lStrQuery = lStrQuery & ", " & lIntColumnId
          lStrQuery = lStrQuery & ", " & (lIntColumnIndex + 1)
          lStrQuery = lStrQuery & ", 'PK_" & lStrTableShrtName & "')"
          Try
            mCmdData = New OracleCommand(lStrQuery, con)
            mCmdData.ExecuteNonQuery()
            lBlnRtn = lBlnRtn And True
          Catch ex As Exception
            lBlnRtn = lBlnRtn And False
          End Try
          lIntEffectedRowCount = mDrData.RecordsAffected
        Else
          Return lBlnRtn
        End If
      Next

      If mBlnRgnrt = False Then
        'MessageBox.Show("Table constraints added successfully.")
        Return lBlnRtn
      End If
    Else
      'MessageBox.Show("Table Does Not Exist")
      Return lBlnRtn
    End If
  End Function

  Public Sub mPrSetPrimaryKey( _
              ByVal aIntOldTableId As Integer, _
              ByVal aIntNewTableId As Integer)

    Dim lStrQuery As String = ""
    Dim lIntIndx As Integer = 0
    Dim lIntIndexId As Integer = 0
    Dim lArrlist As New ArrayList
    Dim lObjPrimaryKeyHelper As clsPrimaryKeyHelper = Nothing
    Dim lObjPrimaryKeyItem As clsPrimaryKeyHelper = Nothing

    lStrQuery = "Select max(TTL_INDX_IDXX) From TRDX_TABL_INDX"
    mCmdData = New OracleCommand(lStrQuery, con)
    lIntIndexId = mCmdData.ExecuteScalar

    If IsDBNull(mCmdData.ExecuteScalar) = True Then
      lIntIndexId = 1
    Else
      lIntIndexId = lIntIndexId + 1
    End If

    lStrQuery = ""
    lStrQuery = "SELECT "
    lStrQuery = lStrQuery & "TTL_INDX_TYPE, "
    lStrQuery = lStrQuery & "TTL_COLM_IDXX, "
    lStrQuery = lStrQuery & "TTL_COLM_SEQC_NMBR "
    lStrQuery = lStrQuery & "FROM TRDX_TABL_INDX "
    lStrQuery = lStrQuery & "WHERE TTL_TABL_IDXX = " & aIntOldTableId
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

    If mDrData.HasRows = True Then

      While mDrData.Read
        lObjPrimaryKeyHelper = New clsPrimaryKeyHelper
        lObjPrimaryKeyHelper.mPrpClmnIndx = mDrData("TTL_COLM_IDXX")
        lObjPrimaryKeyHelper.mPrpIndxType = mDrData("TTL_INDX_TYPE")
        lObjPrimaryKeyHelper.mPrpClmnSeqNo = mDrData("TTL_COLM_SEQC_NMBR")
        lArrlist.Add(lObjPrimaryKeyHelper)
      End While

    End If

    If lArrlist.Count <> 0 Then
      For lIntIndx = 0 To lArrlist.Count - 1
        lObjPrimaryKeyItem = lArrlist.Item(lIntIndx)

        lStrQuery = "Insert Into TRDX_TABL_INDX ("
        lStrQuery = lStrQuery & "  TTL_TABL_IDXX "
        lStrQuery = lStrQuery & ", TTL_INDX_IDXX "
        lStrQuery = lStrQuery & ", TTL_INDX_TYPE "
        lStrQuery = lStrQuery & ", TTL_COLM_IDXX "
        lStrQuery = lStrQuery & ", TTL_COLM_SEQC_NMBR )"
        lStrQuery = lStrQuery & " Values ( "
        lStrQuery = lStrQuery & aIntNewTableId
        lStrQuery = lStrQuery & ", " & lIntIndexId
        lStrQuery = lStrQuery & ",'" & lObjPrimaryKeyItem.mPrpIndxType & "'"
        lStrQuery = lStrQuery & ", " & lObjPrimaryKeyItem.mPrpClmnIndx
        lStrQuery = lStrQuery & ", " & lObjPrimaryKeyItem.mPrpClmnSeqNo & ")"
        mCmdData = New OracleCommand(lStrQuery, con)
        mCmdData.ExecuteNonQuery()
        lIntIndexId = lIntIndexId + 1
      Next
    End If

    MessageBox.Show("Regeneration Successful")

  End Sub

  Private Function mFnReturnEnumTableSpaces(ByVal aStrTableSpaceType As String) As EnmTableSpaceTypes
    If aStrTableSpaceType = "USERS" Then
      Return EnmTableSpaceTypes.USERS
    ElseIf aStrTableSpaceType = "INDX" Then
      Return EnmTableSpaceTypes.INDX
    ElseIf aStrTableSpaceType = "TEMP" Then
      Return EnmTableSpaceTypes.TEMP
    End If
  End Function

  Public Function mFnTablesList() As ArrayList

    Dim lStrQuery As String = ""
    Dim lTableArrayList As New ArrayList
    Dim lObjComboSource As clsComboSource = Nothing

    lStrQuery = "Select TABM_TABLE_NAME,TABM_TABLE_ID FROM TRDX_TABLES_MSTR ORDER BY TABM_TABLE_NAME"

    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

    While mDrData.Read
      lObjComboSource = New clsComboSource
      lObjComboSource.mPrpDisplayMember = mDrData("TABM_TABLE_NAME")
      lObjComboSource.mPrpValueMember = mDrData("TABM_TABLE_ID")

      lTableArrayList.Add(lObjComboSource)
    End While

    Return lTableArrayList

  End Function

  Public Function mFnGetColDataType(ByVal aStrBasicDataType As String) As String


    Dim lStrQuery As String = ""


    lStrQuery = "Select Distinct TDCM_FRNT_END_DATA_CATEGORY FROM TRDX_DATA_CATEGORY_MSTR WHERE UPPER(TDCM_DESC) = '" + aStrBasicDataType + "'"
    'lStrQuery = "Select Distinct TDCM_FRNT_END_DATA_CATEGORY FROM TRDX_DATA_CATEGORY_MSTR WHERE TDCM_DATA_CTGRY = '" + aStrBasicDataType + "'"
    mCmdData = New OracleCommand(lStrQuery, con)
    mDrData = mCmdData.ExecuteReader

    While mDrData.Read
      Return mDrData(0)
    End While
    Return ""
  End Function

  Public Function mPrRemoveTableDetails(ByVal aStrTableName As String) As Boolean
    Dim lStrQuery As String
    lStrQuery = "DELETE FROM TRDX_TABLE_COLUMNS WHERE TRDX_TABLE_COLUMNS.TAB_TABLE_ID IN (SELECT TRDX_TABLES_MSTR.TABM_TABLE_ID FROM TRDX_TABLES_MSTR WHERE TRDX_TABLES_MSTR.TABM_TABLE_NAME='" + aStrTableName + "')"
    Try
      mCmdData = New OracleCommand(lStrQuery, con)
      mCmdData.ExecuteNonQuery()
      Return True
    Catch ex As Exception
      Return False
    End Try
  End Function

  Public Function mPrRemoveTable(ByVal aStrTableId As Integer) As Boolean
    Dim lStrQuery As String

    lStrQuery = "DELETE FROM TRDX_TABLES_DTLS WHERE TTD_TABLE_ID=" + aStrTableId.ToString
    Try
      mCmdData = New OracleCommand(lStrQuery, con)
      mCmdData.ExecuteNonQuery()
    Catch EX As Exception
      Return False
    End Try

    lStrQuery = "DELETE FROM TRDX_TABLES_MSTR WHERE TABM_TABLE_ID=" + aStrTableId.ToString
    Try
      mCmdData = New OracleCommand(lStrQuery, con)
      mCmdData.ExecuteNonQuery()
      Return True
    Catch ex As Exception
      Return False
    End Try

  End Function

  Public Function mPrRemoveTableIndexDetails(ByVal aIntTableId As Integer _
                                           , ByVal aStrIndexType As String) As Boolean
    Dim lStrQuery As String
    lStrQuery = "DELETE FROM TRDX_TABL_INDX WHERE TTL_TABL_IDXX = " + aIntTableId.ToString
    lStrQuery = lStrQuery + " AND TTL_INDX_TYPE ='" + aStrIndexType + "'"
    Try
      mCmdData = New OracleCommand(lStrQuery, con)
      mCmdData.ExecuteNonQuery()
      Return True
    Catch ex As Exception
      Return False
    End Try
  End Function

#End Region

#Region " CLASS MEMBER PROPERTIES "
  Public Property mPrpRgnrt() As Boolean
    Get
      Return mBlnRgnrt
    End Get
    Set(ByVal value As Boolean)
      mBlnRgnrt = value
    End Set
  End Property
#End Region

End Class
